﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using HCM_maps_website.Models;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace HCM_maps_website.Process.DAL
{
    public class DiaChiDAO
    {
       public MapDataDataContext datacontext = new MapDataDataContext();
        public DiaChi GetDiaChiByString(string number,string street)
        {
            DiaChi temp = new DiaChi();
            string querySearch = "select * from Address where Number='"+number+"' and Area like N'"+street+"%'";
            DataTable dt = DataProvider.ExecuteQuery(querySearch);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                temp.ID = Convert.ToInt16(dr[0]);
                temp.Number = dr[1].ToString();
                temp.Area = dr[2].ToString();
                temp.X = Convert.ToDouble(dr[3]);
                temp.Y = Convert.ToDouble(dr[4]);
                    
            }
                return temp;
        }
        public DiaChi GetDiaChiNearByString(string number, string street)
        {
            DiaChi temp = new DiaChi();
            DiaChi temp1 = new DiaChi();
            string querySearch = "select * from Address where Area like N'" + street + "%'";
            DataTable dt = DataProvider.ExecuteQuery(querySearch);
            float dis = 1000;
            int m=-1;
            for (int i = 0; i < dt.Rows.Count; i++)
            {         
                    DataRow dr = dt.Rows[i];
                    temp.Number = dr[1].ToString();
                try
                {
                    int sonha = Convert.ToInt16(number);
                    int t=Convert.ToInt16(temp.Number);
                    int Hieu = Math.Abs(t-sonha);
                    if (Hieu <= dis)
                    {
                        dis = Hieu;
                        m = i;
                    }
                }
                catch (Exception ex)
                {
                    
                }
            }
            if (m != -1)
            {
                DataRow dr = dt.Rows[m];
                temp.ID = Convert.ToInt16(dr[0]);
                temp.Number = dr[1].ToString();
                temp.Area = dr[2].ToString();
                temp.X = Convert.ToDouble(dr[3]);
                temp.Y = Convert.ToDouble(dr[4]);
            }
            return temp;
        }
        public Duong FindStreet(string street)
        {
            Duong temp = new Duong();
            string querySearch = "select Street.FullName,NodeInfo.xLocation,NodeInfo.yLocation "+
                                "from StreetSegment,NodeInfo,Street "+
                                "where StreetSegment.StartPoint=NodeInfo.ID and StreetSegment.[Order]='1' "+
                                "and Street.FullName=N'"+street+"' "+
                                "and Street.ID=StreetSegment.IDStreet ";
            DataTable dt = DataProvider.ExecuteQuery(querySearch);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                temp.Name = dr[0].ToString();
                temp.X = Convert.ToDouble(dr[1]);
                temp.Y = Convert.ToDouble(dr[2]);
            }
            return temp;
        }
        public DiaChi FindArea(string area)
        {
            DiaChi temp = new DiaChi();
            string querySearch = "select * from Address where Area like N'" + area + "%'";
            DataTable dt = DataProvider.ExecuteQuery(querySearch);
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                temp.Area = dr[2].ToString();
                temp.X = Convert.ToDouble(dr[3]);
                temp.Y = Convert.ToDouble(dr[4]);
            }
            return temp;
        }
        public IList<DiaChi> FindNotExact(string number, string street)
        {
            using (SqlConnection sqlConnect = new SqlConnection(ConfigurationManager.ConnectionStrings["Map_DataConnectionString"].ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_Address_FindNearAddress";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.Parameters.AddWithValue("@number", number);
                command.Parameters.AddWithValue("@area", street);

                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                SqlDataReader reader = command.ExecuteReader();
                IList<DiaChi> result = new List<DiaChi>();
                while (reader.Read())
                {
                    DiaChi home = new DiaChi();
                    home.Number = Convert.ToString(reader["Number"]);
                    home.X = Convert.ToDouble(reader["xlocation"]);
                    home.Y = Convert.ToDouble(reader["ylocation"]);
                    home.Area = Convert.ToString(reader["Area"]);
                    result.Add(home);
                }
                return result;
            }
        }
    }
}